﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Entity;

namespace DataAccess
{
    public class CustomerDA
    {
        SqlDataAdapter da;

        public DataSet getCustomer()
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectCustomer", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool CreateCustomer(Customer c)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("addCustomer", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
                    command.Parameters["@CustomerName"].Value = c.CustomerName.ToString();

                    command.Parameters.Add("@CustomerPhone", SqlDbType.NVarChar);
                    command.Parameters["@CustomerPhone"].Value = c.CustomerPhone.ToString();

                    command.Parameters.Add("@CustomerEmail", SqlDbType.NVarChar);
                    command.Parameters["@CustomerEmail"].Value = c.CustomerEmail.ToString();

                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public Customer getCustomerById(int id)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("selectCustomerById", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@CustomerID", id));
                    SqlDataReader dr = cmd.ExecuteReader();
                    Customer c = null;
                    if (dr.Read())
                    {
                        int cusId = Int32.Parse(dr["CustomerID"].ToString());
                        String cusName = dr["CustomerName"].ToString();
                        String cusPhone = dr["CustomerPhone"].ToString();
                        String cusEmail = dr["CustomerEmail"].ToString();
                        c = new Customer(cusId, cusName, cusPhone, cusEmail);
                    }
                    connection.Close();
                    return c;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool UpdateCustomer(Customer c)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("updateCustomer", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@CustomerID", SqlDbType.Int);
                    command.Parameters["@CustomerID"].Value = c.CustomerID.ToString();

                    command.Parameters.Add("@CustomerName", SqlDbType.NVarChar);
                    command.Parameters["@CustomerName"].Value = c.CustomerName.ToString();

                    command.Parameters.Add("@CustomerPhone", SqlDbType.NVarChar);
                    command.Parameters["@CustomerPhone"].Value = c.CustomerPhone.ToString();

                    command.Parameters.Add("@CustomerEmail", SqlDbType.NVarChar);
                    command.Parameters["@CustomerEmail"].Value = c.CustomerEmail.ToString();

                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public bool DeleteCustomer(Customer c)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("removeCustomer", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@CustomerID", c.CustomerID));
                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

    }
}
